-- =====================================================
-- 03_create_constraints.sql
-- 添加所有约束 (PK, FK, CHECK, UNIQUE, DEFAULT) 和 索引
-- =====================================================

BEGIN;

-- 1) donors constraints
ALTER TABLE donors
    ADD CONSTRAINT pk_donors PRIMARY KEY (donor_id),
    ADD CONSTRAINT uq_donors_donor_code UNIQUE (donor_code),
    ADD CONSTRAINT uq_donors_email UNIQUE (contact_email),
    ADD CONSTRAINT uq_donors_phone UNIQUE (contact_phone),
    ADD CONSTRAINT chk_donors_age CHECK (age(date_of_birth) >= '18 years' AND age(date_of_birth) <= '40 years'),
    ADD CONSTRAINT chk_donors_blood_type CHECK (blood_type IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-')),
    ADD CONSTRAINT chk_donors_phone CHECK (contact_phone ~ '^\+[0-9]{8,19}$'),  -- +国家代码(1-4位)+号码(7-15位)
    ADD CONSTRAINT chk_donors_height CHECK (height_cm >= 150 AND height_cm <= 210),
    ADD CONSTRAINT chk_donors_weight CHECK (weight_kg >= 45 AND weight_kg <= 120),
    ALTER COLUMN donor_code SET NOT NULL,
    ALTER COLUMN date_of_birth SET NOT NULL,
    ALTER COLUMN blood_type SET NOT NULL,
    ALTER COLUMN is_active SET DEFAULT true,
    ALTER COLUMN registration_date SET DEFAULT CURRENT_TIMESTAMP;

-- 2) sperm_vials constraints
ALTER TABLE sperm_vials
    ADD CONSTRAINT pk_sperm_vials PRIMARY KEY (vial_id),
    ADD CONSTRAINT fk_sperm_vials_donor FOREIGN KEY (donor_id) REFERENCES donors(donor_id) ON DELETE CASCADE,
    ADD CONSTRAINT uq_sperm_vials_donor_collection UNIQUE (donor_id, collection_date),
    ADD CONSTRAINT chk_sperm_vials_motility CHECK (motility_percent BETWEEN 0 AND 100),
    ADD CONSTRAINT chk_sperm_vials_count CHECK (sperm_count_mil >= 0),
    ADD CONSTRAINT chk_sperm_vials_volume CHECK (volume_ml >= 0),
    ADD CONSTRAINT chk_sperm_vials_status CHECK (status IN ('Available', 'Reserved', 'Used', 'Discarded')),
    ALTER COLUMN donor_id SET NOT NULL,
    ALTER COLUMN collection_date SET NOT NULL,
    ALTER COLUMN status SET DEFAULT 'Available';

-- 3) recipients constraints
ALTER TABLE recipients
    ADD CONSTRAINT pk_recipients PRIMARY KEY (recipient_id),
    ADD CONSTRAINT uq_recipients_email UNIQUE (contact_email),
    ADD CONSTRAINT uq_recipients_phone UNIQUE (contact_phone),
    ADD CONSTRAINT chk_recipients_age CHECK (age(date_of_birth) >= '18 years'),
    ADD CONSTRAINT chk_recipients_phone CHECK (contact_phone ~ '^\+[0-9]{8,19}$'),  -- +国家代码(1-4位)+号码(7-15位)
    ALTER COLUMN full_name SET NOT NULL,
    ALTER COLUMN date_of_birth SET NOT NULL,
    ALTER COLUMN registration_date SET DEFAULT CURRENT_TIMESTAMP;

-- 4) medical_screenings constraints
ALTER TABLE medical_screenings
    ADD CONSTRAINT pk_medical_screenings PRIMARY KEY (screening_id),
    ADD CONSTRAINT fk_medical_screenings_donor FOREIGN KEY (donor_id) REFERENCES donors(donor_id) ON DELETE CASCADE,
    ALTER COLUMN donor_id SET NOT NULL,
    ALTER COLUMN screening_date SET NOT NULL,
    ALTER COLUMN test_name SET NOT NULL,
    ALTER COLUMN result SET NOT NULL;

-- 5) partner_clinics constraints
ALTER TABLE partner_clinics
    ADD CONSTRAINT pk_partner_clinics PRIMARY KEY (clinic_id),
    ADD CONSTRAINT uq_partner_clinics_name UNIQUE (clinic_name),
    ADD CONSTRAINT chk_partner_clinics_phone CHECK (contact_phone ~ '^\+[0-9]{8,19}$'),  -- +国家代码(1-4位)+号码(7-15位)
    ALTER COLUMN clinic_name SET NOT NULL,
    ALTER COLUMN address SET NOT NULL;

-- 6) staff constraints
ALTER TABLE staff
    ADD CONSTRAINT pk_staff PRIMARY KEY (staff_id),
    ADD CONSTRAINT uq_staff_username UNIQUE (username),
    ADD CONSTRAINT chk_staff_role CHECK (role IN ('Admin', 'Doctor', 'Nurse', 'Lab_Technician', 'Coordinator')),
    ALTER COLUMN username SET NOT NULL,
    ALTER COLUMN password_hash SET NOT NULL,
    ALTER COLUMN full_name SET NOT NULL,
    ALTER COLUMN is_active SET DEFAULT true;

-- 7) orders constraints
ALTER TABLE orders
    ADD CONSTRAINT pk_orders PRIMARY KEY (order_id),
    ADD CONSTRAINT fk_orders_recipient FOREIGN KEY (recipient_id) REFERENCES recipients(recipient_id),
    ADD CONSTRAINT fk_orders_clinic FOREIGN KEY (clinic_id) REFERENCES partner_clinics(clinic_id),
    ADD CONSTRAINT fk_orders_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL,
    ADD CONSTRAINT chk_orders_status CHECK (status IN ('Pending', 'Paid', 'Shipped', 'Completed', 'Cancelled')),
    ADD CONSTRAINT chk_orders_price CHECK (total_price >= 0),
    ALTER COLUMN recipient_id SET NOT NULL,
    ALTER COLUMN clinic_id SET NOT NULL,
    ALTER COLUMN order_date SET DEFAULT CURRENT_TIMESTAMP;

-- 8) shipments constraints
ALTER TABLE shipments
    ADD CONSTRAINT pk_shipments PRIMARY KEY (shipment_id),
    ADD CONSTRAINT uq_shipments_order UNIQUE (order_id),
    ADD CONSTRAINT fk_shipments_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_shipments_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL,
    ADD CONSTRAINT chk_shipments_status CHECK (status IN ('Preparing', 'In Transit', 'Delivered', 'Failed')),
    ALTER COLUMN order_id SET NOT NULL;

-- 9) offspring_tracking constraints
ALTER TABLE offspring_tracking
    ADD CONSTRAINT pk_offspring_tracking PRIMARY KEY (offspring_id),
    ADD CONSTRAINT fk_offspring_donor FOREIGN KEY (donor_id) REFERENCES donors(donor_id),
    ADD CONSTRAINT fk_offspring_recipient FOREIGN KEY (recipient_id) REFERENCES recipients(recipient_id),
    ALTER COLUMN donor_id SET NOT NULL,
    ALTER COLUMN recipient_id SET NOT NULL,
    ALTER COLUMN date_of_birth SET NOT NULL,
    ALTER COLUMN report_date SET DEFAULT CURRENT_DATE;

-- 10) audit_logs constraints
ALTER TABLE audit_logs
    ADD CONSTRAINT pk_audit_logs PRIMARY KEY (log_id),
    ADD CONSTRAINT fk_audit_logs_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL,
    ALTER COLUMN log_time SET DEFAULT CURRENT_TIMESTAMP;

-- 11) order_items constraints
ALTER TABLE order_items
    ADD CONSTRAINT pk_order_items PRIMARY KEY (order_item_id),
    ADD CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_order_items_vial FOREIGN KEY (vial_id) REFERENCES sperm_vials(vial_id),
    -- 移除 UNIQUE (vial_id) 约束，允许样本在不同订单中重复使用（如订单取消后重新分配）
    -- 业务逻辑层会确保同一时间样本只被一个活跃订单使用
    ADD CONSTRAINT chk_order_items_quantity CHECK (quantity >= 1),
    ADD CONSTRAINT chk_order_items_price CHECK (price_per_unit >= 0),
    ALTER COLUMN order_id SET NOT NULL,
    ALTER COLUMN vial_id SET NOT NULL,
    ALTER COLUMN quantity SET DEFAULT 1;

-- 12) donor_clinics constraints
ALTER TABLE donor_clinics
    ADD CONSTRAINT pk_donor_clinics PRIMARY KEY (donor_id, clinic_id),
    ADD CONSTRAINT fk_donor_clinics_donor FOREIGN KEY (donor_id) REFERENCES donors(donor_id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_donor_clinics_clinic FOREIGN KEY (clinic_id) REFERENCES partner_clinics(clinic_id) ON DELETE CASCADE,
    ALTER COLUMN donor_id SET NOT NULL,
    ALTER COLUMN clinic_id SET NOT NULL;

-- 13) Indexes
CREATE INDEX IF NOT EXISTS idx_sperm_vials_donor_id ON sperm_vials(donor_id);
CREATE INDEX IF NOT EXISTS idx_medical_screenings_donor_id ON medical_screenings(donor_id);
CREATE INDEX IF NOT EXISTS idx_orders_recipient_id ON orders(recipient_id);
CREATE INDEX IF NOT EXISTS idx_orders_clinic_id ON orders(clinic_id);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_vial_id ON order_items(vial_id);

COMMIT;